<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>eval: common_schema documentation</title>
	<meta name="description" content="eval: common_schema" />
	<meta name="keywords" content="eval: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="eval.html">eval</a></h2>	
<h3>NAME</h3>
eval(): Evaluates the queries generated by a given query.

<h3>TYPE</h3>
Procedure

<h3>DESCRIPTION</h3>
<p>
	Evaluates the queries generated by a given query.
	Given <strong>sql_query</strong> is expected to be a SQL generating query. 
	That is, it is expected to produce,	when invoked, a single text column consisting of SQL queries. 
	The column may contain one or more queries per row. 
	If multiple queries provided, the semicolon (<strong>;</strong>) delimiter is expected to terminate each.
	The last query does not have to be terminated with a semicolon.
</p>
<p>
	The <strong>eval()</strong> procedure will invoke said queries, and then invoke (evaluate) any of 
	the resulting queries.
</p>
<p>
	Invoker of this procedure must have the <strong>CREATE TEMPORARY TABLES</strong> privilege, 
	as well as any privileges required for evaluating implied queries.
</p>

<p>
	Many of <i>common_schema</i>'s views include SQL columns: columns containing
	read-to-invoke statements. Consider
	<a href="processlist_grantees.html">processlist_grantees</a>, 
	<a href="redundant_keys.html">redundant_keys</a>, 
	<a href="sql_accounts.html">sql_accounts</a>, 
	<a href="sql_alter_table.html">sql_alter_table</a>, 
	<a href="sql_foreign_keys.html">sql_foreign_keys</a>, 
	<a href="sql_grants.html">sql_grants</a>,
	<a href="sql_range_partitions.html">sql_range_partitions</a>
	and more. 
	Thus, it is possible to invoke <i>eval()</i> directly on such views, see examples below.	
</p>

<p>
	<i>common_schema</i> offers alternatives to <i>eval()</i>, on popular use cases.
	In particular, see QueryScript's <a href="query_script_foreach.html">foreach</a> statement
	for many operations that can be produced via INFORMATION_SCHEMA.TABLES. 
</p>

<p>
	QueryScript also offers the <a href="query_script_eval.html">eval</a> statement, built into
	the language, which uses exact same logic as this routine (and in fact relies on it).
</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>eval(sql_query TEXT CHARSET utf8) 
  MODIFIES SQL DATA</pre></blockquote>
</p>
<p>
  Input:
  <ul>
    <li><strong>sql_query</strong>: a query which generates SQL queries to be evaluated. Must return with exactly one column.</li>
  </ul>
  This procedure relies on <a href="exec_single.html">exec_single()</a>, which means it respects:
  <ul>
    <li><strong>@common_schema_dryrun</strong>: when <strong>1</strong>, queries are not executed, but rather printed.</li>
    <li><strong>@common_schema_verbose</strong>: when <strong>1</strong>, queries are verbosed.</li>
  </ul>
</p>

<h3>EXAMPLES</h3>
	<p>
		In the following example we kill all connections executing queries for more than 
		<strong>20</strong> seconds.
	<blockquote><pre>mysql&gt; SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+------------+---------------------+
| Id | User | Host      | db            | Command | Time | State      | Info                |
+----+------+-----------+---------------+---------+------+------------+---------------------+
|  2 | root | localhost | common_schema | Query   |    0 | NULL       | SHOW PROCESSLIST    |
| 43 | apps | localhost | NULL          | Query   |   28 | User sleep | select sleep(10000) |
+----+------+-----------+---------------+---------+------+------------+---------------------+
2 rows in set (0.00 sec)

mysql&gt; CALL eval('SELECT CONCAT(\'KILL \',id) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 20');

mysql&gt; SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+-------+------------------+
| Id | User | Host      | db            | Command | Time | State | Info             |
+----+------+-----------+---------------+---------+------+-------+------------------+
|  2 | root | localhost | common_schema | Query   |    0 | NULL  | SHOW PROCESSLIST |
+----+------+-----------+---------------+---------+------+-------+------------------+
1 row in set (0.00 sec)
</pre></blockquote>
	As per previous note on <i>common_schema</i> views providing with SQL columns,
	see an alternative to the above (and slightly more sophisticated), 
	utilizing <a href="processlist_grantees.html">processlist_grantees</a>: 
	<blockquote><pre>mysql&gt; CALL eval("SELECT sql_kill_query FROM processlist_grantees WHERE COMMAND != 'Sleep' AND TIME > 20 AND is_super = 0 AND is_repl = 0");
</pre></blockquote>
	</p>

	<p>
		Automatically add partition to a <strong>RANGE</strong> partition table
		(see <a href="sql_range_partitions.html">sql_range_partitions</a>):
	</p>
	<blockquote><pre>mysql&gt; CALL eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
</pre></blockquote>

	<p>
		Block accounts for user <strong>'gromit'</strong>
		(see <a href="sql_accounts.html">sql_accounts</a>):
	</p>
	<blockquote><pre>mysql&gt; CALL eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit'");
</pre></blockquote>


<p>Kill transactions idle for <strong>30</strong> seconds or more:
<blockquote><pre>mysql&gt; CALL eval("SELECT sql_kill_query FROM common_schema.innodb_transactions WHERE trx_idle_seconds >= 30");
</pre></blockquote>
</p>


<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="query_script_eval.html">eval</a>,
<a href="exec.html">exec()</a>,
<a href="exec_single.html">exec_single()</a>,
<a href="foreach.html">foreach()</a>,
<a href="repeat_exec.html">repeat_exec()</a>

<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
